package dbLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import modelLayer.Clothing;
import modelLayer.Equipment;
import modelLayer.GunReplicas;
import modelLayer.Product;
import modelLayer.Supplier;

public class DbProduct {
	private Connection con;

	public DbProduct(){
		con = DbConnection.getInstance().getDBcon();
	}

	/**
	 * Get all Clothing objects
	 * 
	 * @return ArrayList<Clothing> list
	 */
	public ArrayList<Clothing> retrieveAllClothing(){
		return multiClothing("");
	}

	/**
	 * Get all Equipment objects
	 * 
	 * @return ArrayList<Equipment> list
	 */
	public ArrayList<Equipment> retrieveAllEquipment(){
		return multiEquipment("");
	}

	/**
	 * Get all GunReplica objects
	 * 
	 * @return ArrayList<GunReplicas> list
	 */
	public ArrayList<GunReplicas> retrieveAllGunReplicas(){
		return multiGunReplicas("");
	}

	/**
	 * Get all product objects
	 * 
	 * @return ArrayList<Product> list
	 */
	public ArrayList<Product> retrieveAllProducts(){
		return buildAllProductList();
	}

	/**
	 * Get a specific product by the product ID
	 * 
	 * @param productId The id of the wanted product
	 * @return The wanted product
	 */
	public Product retrieveProductFromDb(int productId){
		ArrayList<Product> pList = retrieveAllProducts();
		Product pObj = null;
		for(Product p : pList){
			if(p.getProductId() == productId){
				pObj = p;
			}
			//break;
		}
		return pObj;
	}

	/**
	 * Insert product into Db
	 * 
	 * @param pro Product
	 * @return A integer representing the rows affected.
	 * @throws Exception Exception if the insert is not succesfull.
	 */
	public int insertProduct(Product pro) throws Exception{
		int rc = -1;
		Clothing c = null;
		Equipment e = null;
		GunReplicas g = null;
		String query = null;

		if(pro instanceof Clothing){
			c = (Clothing) pro;
			query = "INSERT INTO product(name, purchasePrice, salesPrice, rentPrice, countryOfOri, minimumStock, color, size, supplierId) VALUES ('"
					+ c.getName() + "', " + c.getPurchasePrice() + ", " + c.getSalesPrice() + ", " + c.getRentPrice() + ", '" + c.getCountryOfOri() +
					"', " + c.getMinStock() + ", '" + c.getColor() + "', '" + c.getSize()+ "'," + c.getSupplier().getPartnerId()+")"; 
			System.out.println(query);
		}
		if(pro instanceof Equipment){
			e = (Equipment) pro;
			query = "INSERT INTO product (name, purchasePrice, salesPrice, rentPrice, countryOfOri, minimumStock, description, equipmentType, supplierId) VALUES ('"
					+ e.getName() + "', " + e.getPurchasePrice() + ", " + e.getSalesPrice() + ", " + e.getRentPrice() + ", '" + e.getCountryOfOri() +
					"', " + e.getMinStock() + ", '" + e.getDescription() + "', '" + e.getType() + "', "+e.getSupplier()+")";
			System.out.println(query);
		}
		if(pro instanceof GunReplicas){
			g = (GunReplicas) pro;
			query = "INSERT INTO product (name, purchasePrice, salesPrice, rentPrice, countryOfOri, minimumStock, calibre, material, supplierId) VALUES ('"
					+ g.getName() + "', " + g.getPurchasePrice() + ", " + g.getSalesPrice() + ", " + g.getRentPrice() + ", '" + g.getCountryOfOri() +
					"', " + g.getMinStock() + ", '" + g.getCalibre() + "', '" + g.getMaterial() + "', " + g.getSupplier()+")";
			System.out.println(query);
		}		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}catch(SQLException sqlE){
			System.out.println("Product object not created");
			throw new Exception("Product is not inserted");
		}
		return (rc);
	}

	/**
	 * Update product object
	 * 
	 * @param pro Product
	 * @return Integer representing the rows affected.
	 */
	public int updateProduct(Product pro){
		int rc = -1;
		Clothing c = null;
		Equipment e = null;
		GunReplicas g = null;
		String query = null;
		Product p = pro;

		if(pro instanceof Clothing){
			c = (Clothing) pro;
			query = "UPDATE product SET name = '"+c.getName() +"', purchasePrice = '"+ c.getPurchasePrice() +
					"', salesPrice = '" + c.getSalesPrice() + "', rentPrice = '" + c.getRentPrice() + "', countryOfOri = '"+
					c.getCountryOfOri() + "', minimumStock = '" + c.getMinStock() + "', color = '" + c.getColor() +
					"', size = '" + c.getSize() + "' WHERE productId = '" + c.getProductId()+"'";
		}
		if(pro instanceof Equipment){
			e = (Equipment) pro;
			query = "UPDATE product SET name = '"+e.getName() +"', purchasePrice = '"+ e.getPurchasePrice() +
					"', salesPrice = '" + e.getSalesPrice() + "', rentPrice = '" + e.getRentPrice() + "', countryOfOri = '"+
					e.getCountryOfOri() + "', minimumStock = '" + e.getMinStock() + "', description = '" + e.getDescription() +
					"', equipmentType = '" + e.getType()+ "' WHERE productId = '" + e.getProductId()+"'";
		}
		if(pro instanceof GunReplicas){
			g = (GunReplicas) pro;
			query = "UPDATE product SET name = '"+g.getName() +"', purchasePrice = '"+ g.getPurchasePrice() +
					"', salesPrice = '" + g.getSalesPrice() + "', rentPrice = '" + g.getRentPrice() + "', countryOfOri = '"+
					g.getCountryOfOri() + "', minimumStock = '" + g.getMinStock() + "', calibre = '" + g.getCalibre() +
					"', material = '" + g.getMaterial() + "' WHERE productId = '" + g.getProductId()+"'";
		}

		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}catch(Exception exp){
			System.out.println("Error updating Product.");
		}
		return rc;
	}

	/*
	 * Delete product from db
	 * */
	/**
	 * Delete product from db
	 * 
	 * @param productId The id of the product to be deleted
	 * @return Integer representing the rows affected.
	 */
	public int deleteProduct(int productId){
		int rc = -1;
		String query = "DELETE FROM product WHERE productId = '" + productId + "'";
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}catch(Exception e){
			System.out.println("Product is not deleted.");
		}
		return rc;
	}

	/**
	 * Retrieve product by name.
	 * returns a list of all products (Clothing, Equipment and GunReplicas) with that name
	 * @param String name
	 * @return ArrayList<Product> list
	 * */
	public ArrayList<Product> retrieveProductByName(String name){
		ArrayList<Product> list = new ArrayList<Product>();
		ArrayList<Product> tempList = retrieveAllProducts();

		for(Product p: tempList){
			if(p.getName() == name){
				list.add(p);
			}
		}
		return list;
	}


	/**
	 * Build query to fetch all data from Product
	 * 
	 * @param where The where clause for the SQL database
	 * @return
	 */
	private String buildQuery(String where){
		String query = "SELECT * FROM product ";

		if(where.length() > 0)
			query = query + " WHERE " + where;

		return query;
	}

	/**
	 * Method for building all the products from the database. 
	 * 
	 * @return A arraylist with all products.
	 */
	private ArrayList<Product> buildAllProductList(){
		ArrayList<Product> list = new ArrayList<Product>();
		ArrayList<Clothing> clothList = retrieveAllClothing();
		ArrayList<Equipment> equipList = retrieveAllEquipment();
		ArrayList<GunReplicas> gunList = retrieveAllGunReplicas();
		//Add all clothing object to list
		for(Clothing c: clothList){
			list.add(c);
		}
		//Add all equipment objects to list
		for(Equipment e: equipList){
			list.add(e);
		}
		//Add all gunreplica objects to list
		for(GunReplicas g: gunList){
			list.add(g);
		}
		return list;
	}

	/**
	 * Method for building clothing objects.
	 * 
	 * @param results A set of results containing data concerning clothing objects.
	 * @return A clothing object.
	 */
	private Clothing buildClothing(ResultSet results){
		Clothing pC = new Clothing();
		try{
			if(results.getString("description") == null && results.getString("calibre") == null){
				pC.setName(results.getString("name"));
				pC.setCountryOfOri(results.getString("countryOfOri"));
				pC.setMinStock(results.getInt("minimumStock"));
				pC.setProductId(results.getInt("productId"));
				pC.setPurchasePrice(results.getDouble("purchasePrice"));
				pC.setRentPrice(results.getDouble("rentPrice"));
				pC.setSalesPrice(results.getDouble("salesPrice"));
				pC.setColor(results.getString("color"));
				pC.setSize(results.getString("size"));
				pC.setSupplier(buildSupplier(Integer.parseInt(results.getString("supplierId"))));
				return pC;
			}else
				System.out.println("Could not create clothing in buildClothing method.");
		}catch(Exception e){
			System.out.println("Error building Clothing object.");
		}
		return null;
	}

	/**
	 * Method for building clothing objects.
	 * 
	 * @param results A set of results containing data concerning Equipment objects.
	 * @return A Equipment object.
	 */
	private Equipment buildEquipment(ResultSet results){
		Equipment pE = new Equipment();
		try{
			if(results.getString("size") == null && results.getString("calibre") == null){
				pE.setName(results.getString("name"));
				pE.setCountryOfOri(results.getString("countryOfOri"));
				pE.setMinStock(results.getInt("minimumStock"));
				pE.setProductId(results.getInt("productId"));
				pE.setPurchasePrice(results.getDouble("purchasePrice"));
				pE.setRentPrice(results.getDouble("rentPrice"));
				pE.setSalesPrice(results.getDouble("salesPrice"));
				pE.setDescription(results.getString("description"));
				pE.setType(results.getString("equipmentType"));
				pE.setSupplier(buildSupplier(Integer.parseInt(results.getString("supplierId"))));
				return pE;
			}else
				System.out.println("Could not create equipment in buildEquipment method.");
		}catch(Exception e){
			System.out.println("Error building equipment object.");
		}
		return null;
	}
	/**
	 * Method for building GunReplica objects.
	 * 
	 * @param results A set of results containing data concerning GunReplica objects.
	 * @return A GunReplica object.
	 */
	private GunReplicas buildGunReplicas(ResultSet results){
		GunReplicas pG = new GunReplicas();
		try{
			if(results.getString("size") == null && results.getString("description") == null){
				pG.setName(results.getString("name"));
				pG.setCountryOfOri(results.getString("countryOfOri"));
				pG.setMinStock(results.getInt("minimumStock"));
				pG.setProductId(results.getInt("productId"));
				pG.setPurchasePrice(results.getDouble("purchasePrice"));
				pG.setRentPrice(results.getDouble("rentPrice"));
				pG.setSalesPrice(results.getDouble("salesPrice"));
				pG.setCalibre(results.getString("calibre"));
				pG.setMaterial(results.getString("material"));
				pG.setSupplier(buildSupplier(Integer.parseInt(results.getString("supplierId"))));
				return pG;
			}else
				System.out.println("Could not create GunReplicas in buildGunReplicas method.");
		}catch(Exception e){
			System.out.println("Error building new GunReplicas object.");
		}
		return null;
	}

	/**
	 * Method for getting a single clothing object.
	 * 
	 * @param where SQL where clause
	 * @return Clothing object
	 */
	private Clothing singleClothing(String where){
		ResultSet results;
		Clothing pC = new Clothing();

		//Build query
		String query = buildQuery(where);
		System.out.println(query);//To be deleted after test

		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			if(results.next()){
				if(buildClothing(results) != null){
					pC = buildClothing(results);
				}
				stmt.close();
			}
			else
				pC = null;

		}catch(Exception e){
			System.out.println("No clothings was found.");
		}
		return pC;
	}
	
	/**
	 * Method for getting multiple clothing objects.
	 * 
	 * @param where SQL where clause
	 * @return A list of clothing objects.
	 */
	private ArrayList<Clothing> multiClothing(String where){
		ResultSet results;
		ArrayList<Clothing> list = new ArrayList<Clothing>();

		//Build query
		String query = buildQuery(where);
		System.out.println(query);//Delete after tests

		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			while(results.next()){
				Clothing pC = new Clothing();
				if(buildClothing(results) != null){
					pC = buildClothing(results);
					list.add(pC);
				}
			}
			stmt.close();
		}catch(Exception e){
			System.out.println("Error building arraylist of Clothing objects.");
		}
		return list;
	}

	/**
	 * Method for getting a single Equipment object
	 * 
	 * @param where SQL where clause
	 * @return Equipment object
	 */
	private Equipment singleEquipment(String where){
		ResultSet results;
		Equipment pE = new Equipment();

		String query = buildQuery(where);
		System.out.println("Error building single equipment");

		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			if(results.next()){
				if(buildEquipment(results) != null) {
					pE = buildEquipment(results);
				}
				stmt.close();
			}else
				pE = null;
		}catch(Exception e){
			System.out.println("Error building equipment object");
		}
		return pE;
	}
	
	/**
	 * Method for getting multiple Equipment objects
	 * 
	 * @param where SQL where clause
	 * @return A list of Equipment objects
	 */
	private ArrayList<Equipment> multiEquipment(String where){
		ResultSet results;
		ArrayList<Equipment> list = new ArrayList<Equipment>();

		String query = buildQuery(where);
		System.out.println(query);//Delete after tests

		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			while(results.next()){
				Equipment pE = new Equipment();
				if(buildEquipment(results) != null){
					pE = buildEquipment(results);
					list.add(pE);
				}
			}
			stmt.close();
		}catch(Exception e){
			System.out.println("Error building arraylist of equipment objects.");
		}
		return list;
	}
	/**
	 * Method for getting a single GunReplica object.
	 * 
	 * @param where SQL where clause
	 * @return GunReplica object.
	 */
	private GunReplicas singleGunReplicas(String where){
		ResultSet results;
		GunReplicas pG = new GunReplicas();

		String query = buildQuery(where);
		System.out.println(query);//Delete after tests

		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			if(results.next()){
				if(buildGunReplicas(results) != null){
					pG = buildGunReplicas(results);
				}
				stmt.close();
			}else
				pG = null;
		}catch(Exception e){
			System.out.println("Error building single GunReplicas object");
		}
		return pG;
	}
	
	/**
	 * Method for getting multiple GunReplica objects
	 * 
	 * @param where SQL where clause
	 * @return A list of GunReplica objects
	 */
	private ArrayList<GunReplicas> multiGunReplicas(String where){
		ResultSet results;
		ArrayList<GunReplicas> list = new ArrayList<GunReplicas>();

		String query = buildQuery(where);
		System.out.println(query);//Delete after tests
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			while(results.next()){
				GunReplicas pG = new GunReplicas();
				if(buildGunReplicas(results) != null){
					pG = buildGunReplicas(results);
					list.add(pG);
				}
			}
			stmt.close();
		}catch(Exception e){
			System.out.println("Error building multi GunReplicas.");
		}
		return list;
	}

	/**
	 * Method for building a supplierObject, utilizing the DBPartner class.
	 * 
	 * @param supplierId The id of the supplier to be built
	 * @return A supplier object
	 */
	private Supplier buildSupplier(int supplierId) {
		DbPartner dbp = new DbPartner();
		Supplier supObj = new Supplier();
		supObj = dbp.retrieveSupplierFromDb(supplierId);
		return supObj;
	}
}